package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.ArticleDao;
import com.ws.shop.entity.Article;
import com.ws.shop.util.JdbcUtil;

@Repository(value="articleDao")
public class ArticleDaoImpl  extends BaseDaoImpl implements ArticleDao{
	private static Logger log = Logger.getLogger(ArticleDaoImpl.class);
	
	String articlefiled = "a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state";
	//获取数据

	//cid = 公告分类 cid =1 公告分类1 ,state = 1 ,限制5条数据
	public List<Map<String,Object>> getArticle(int state ,int cid,int limit){ //感言分类2
		String s = "select "+articlefiled+" from jvf_article as a where cid = ? and state =?  order by a.order desc limit "+limit;
		log.info(" getArticle sql : " + s);
		return this.queryForList(s, new Object[]{new Integer(cid),new Integer(state)});
	}

	final String findArticleListPage_count = "select count(1) from jvf_article a";
	final String findArticleListPage_list = "select a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state,ac.name cname from jvf_article a left join jvf_article_categories ac on a.cid=ac.id";
	final String findArticleListPage_order = " order by a.id desc";
	public Page findArticleListPage(long pageno,int pagesize,String title) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(title != null){
			query.append(" and a.title like '%").append(title).append("%'");
		}
		return findPage(pageno,pagesize,new StringBuilder(findArticleListPage_count).append(query).toString()
				,new StringBuilder(findArticleListPage_list).append(query).append(findArticleListPage_order));
	}

	final String deleteArticleById = "delete from jvf_article where id=?";
	public void deleteArticleById(int id) {
		this.update(deleteArticleById, id);
	}

	public void deleteArticleByIds(final int[] ids) {
		this.batchUpdate(deleteArticleById, new BatchPreparedStatementSetter(){
			public int getBatchSize() {
				return ids.length;
			}

			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				ps.setInt(1, ids[i]);
			}
		});
	}

	final String findArticleById = "select cid,title,link,`order`,keywords,description,edittime,body,state from jvf_article where id=?";
	public Article findArticleById(final int id) {
		return this.query(findArticleById, new ResultSetExtractor<Article>(){
			public Article extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(!rs.next())
					return null;
				Article a = new Article();
				a.setId(id);
				a.setCid(rs.getInt(1));
				a.setTitle(rs.getString(2));
				a.setLink(rs.getString(3));
				a.setOrder(rs.getInt(4));
				a.setKeywords(rs.getString(5));
				a.setDescription(rs.getString(6));
				a.setEdittime(rs.getTimestamp(7));
				a.setBody(rs.getString(8));
				a.setState(rs.getInt(9));
				return a;
			}
		},id);
	}

	
	
	final String save_sql = "insert into jvf_article(cid,title,link,`order`,keywords,description,edittime,body,state) values(?,?,?,?,?,?,now(),?,?)";
	final String update_sql = "update jvf_article set cid=?,title=?,link=?,`order`=?,keywords=?,description=?,edittime=now(),body=?,state=? where id=?";
	public void saveOrUpdateArticle(Article article) {
		if(article.getId() == 0){
			this.update(save_sql,article.getCid(),article.getTitle(),article.getLink(),article.getOrder(),article.getKeywords(),article.getDescription(),article.getBody(),article.getState());
		}else{
			this.update(update_sql,article.getCid(),article.getTitle(),article.getLink(),article.getOrder(),article.getKeywords(),article.getDescription(),article.getBody(),article.getState(),article.getId());
		}
	}
	
	
	public List<Article> findArticlesByCategery(){
		String s = "select ar.id,ar.cid,ar.title,ar.link,ar.order,ar.keywords,ar.description,ar.edittime,ar.body,ar.state from  jvf_article ar ,jvf_article_categories ac   where  ac.id = ar.cid AND ac.fid = 8";
		return this.query(s, new ResultSetExtractor<List<Article>>(){
			public List<Article> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				List<Article> list = new LinkedList<Article>();
				Article ac = null;
				while(rs.next()){
					ac = new Article();
					ac.setId(rs.getInt(1));
					ac.setCid(rs.getInt(2));
					ac.setTitle(rs.getString(3));
					ac.setOrder(rs.getInt(5));
					ac.setEdittime(rs.getTimestamp(8));
					ac.setBody(rs.getString(9));
					ac.setState(rs.getInt(10));
					list.add(ac);
				}
				return list;
			}
		});
	}
	
	/**
	 * 类型查文章
	 * @param page
	 * @param cid
	 */
	public void getArticlesByCid(final Page page, final int cid,final int state){
		 this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							PreparedStatement psmt = null;
							ResultSet rs = null;
							try {
								String s = "select count(1) from jvf_article where cid = " + cid +" state = " + state;
								Statement stmt = connect.createStatement();
								ResultSet r = stmt.executeQuery(s);
								long totalnum = 0;
								if (r.next()) {
									totalnum = r.getLong(1);
									page.setTotalnum(totalnum);
								}
								JdbcUtil.close(r,stmt,null);
								s = "select " + articlefiled + "  from jvf_article a  where  a.cid = ? and a.state = ? order by a.order desc limit ?,?";
								psmt = connect.prepareStatement(s);
								psmt.setInt(1, cid);
								psmt.setInt(2, state);
								psmt.setLong(3, page.getStartrownum());
								psmt.setInt(4, page.getPagesize());
								rs = psmt.executeQuery();
								while(rs.next()){
									Article ac = new Article();
									ac.setId(rs.getInt(1));
									ac.setCid(rs.getInt(2));
									ac.setTitle(rs.getString(3));
									ac.setOrder(rs.getInt(5));
									ac.setEdittime(rs.getTimestamp(8));
									ac.setBody(rs.getString(9));
									ac.setState(rs.getInt(10));
									page.addData(ac);
								}
								return null;
							} catch (Exception e) {
								e.printStackTrace();
								log.error("getArticlesByCid!",e);
								return null;
							} finally {
								JdbcUtil.close(rs, psmt, null);
				      		}
				}
	      });
	}
	
	public List<Map<String,Object>> getArticlesByCondition(long id,int cid,int type){
		String s = "select " + articlefiled + "  from jvf_article a  where ";
		if(type == 1){
			s = s + " a.id < " + id ;
		}else if(type == 2){
			s = s + " a.id > " + id ;
		}else if(type == 3){
			s = s + " a.id = " + id ;
		}else{
			s = s + " 1 = 1";
		}
		s = s + " and " + " cid = " + cid;
		return this.queryForList(s, new Object[]{new Long(id),new Integer(cid)});
	}

	final String findArticlesByCids_count = "select count(1) from jvf_article a";
	final String findArticlesByCids_list = "select a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state from jvf_article a";
	final String findArticlesByCids_order = " order by a.id desc";
	public Page findArticlesByCids(int pageno, int pagesize, int[] cids,
			String start, String end) {
		StringBuilder query = new StringBuilder(" where a.state=1");
		if(cids != null && cids.length > 0){
		query.append(" and a.cid in(");
			for(int i = 0 ; i < cids.length ; i++){
				query.append(cids[i]);
				if(i < cids.length-1){
					query.append(",");
				}
			}
			query.append(")");
		}
		if(end != null){
			query.append(" and a.edittime<='").append(end).append(" 23:59:59'");
		}
		if(start != null){
			query.append(" and a.edittime>='").append(start).append(" 00:00:00'");
		}
		Page page = new Page(pageno);
		if(pagesize > 0){
			page.setPagesize(pagesize);
		}
		page.setTotalnum(this.queryForInt(new StringBuilder(findArticlesByCids_count).append(query).toString()));
		if(page.getTotalnum() > 0){
			List<Article> list = this.query(new StringBuilder(findArticlesByCids_list).append(query).append(findArticlesByCids_order)
					.append(" limit ").append(page.getStartrownum()).append(",").append(page.getPagesize()).toString(),
					new ResultSetExtractor<List<Article>>(){
						public List<Article> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							Article ac = null;
							List<Article> list = new LinkedList<Article>();
							while(rs.next()){
								ac = new Article();
								ac.setId(rs.getInt(1));
								ac.setCid(rs.getInt(2));
								ac.setTitle(rs.getString(3));
								ac.setLink(rs.getString(4));
								ac.setOrder(rs.getInt(5));
								ac.setKeywords(rs.getString(6));
								ac.setDescription(rs.getString(7));
								ac.setEdittime(rs.getTimestamp(8));
								ac.setBody(rs.getString(9));
								ac.setState(rs.getInt(10));
								list.add(ac);
							}
							return list;
						}
			});
			page.setData(list);
		}
		return page;
	}

	final String findArticleListPageCid_count = "select count(1) from jvf_article a";
	final String findArticleListPageCid_list = "select a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state,ac.name cname from jvf_article a left join jvf_article_categories ac on a.cid=ac.id";
	final String findArticleListPageCid_order = " order by a.id desc";
	public Page findArticleListPage(int pageno, int pagesize, int cid) {
		StringBuilder query = new StringBuilder(" where a.cid=").append(cid);
		return findPage(pageno,pagesize,new StringBuilder(findArticleListPageCid_count).append(query).toString()
				,new StringBuilder(findArticleListPageCid_list).append(query).append(findArticleListPageCid_order));
	}
	
}
